package infraestructura;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import modelo.Product;

public class ProductPoolPersistence implements ProductPersistence {

    private DataSource pool = null;
    private static ProductPoolPersistence persistence = null;
    
    private ProductPoolPersistence() {
        Context env = null;
        try {
            env = (Context) new InitialContext().lookup("java:comp/env");
            pool = (DataSource) env.lookup("jdbc/ShoppersPool");
            if (pool == null) {
                throw new RuntimeException("No se ha podido obtener la conexion");
            }
        } catch (NamingException ex) {
            ex.printStackTrace();
        }
    }
    
    public static ProductPersistence getPersistenceManager() {
        if (persistence == null) {
            persistence = new ProductPoolPersistence();
        }
        return persistence;
    }
    
    @Override
    public boolean exists(String name) {
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT COUNT(*) "
                    + "FROM producto "
                    + "WHERE Producto_nombre = ?;");

            statment.setString(1, name);
            
            statment.execute();
            resultSet = statment.getResultSet();

            resultSet.first();
            
            return resultSet.getInt(1) > 0;
        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //@Abraham falta  resultSet.close();
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        return false;
    }

    @Override
    public boolean exists(int id) {
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT COUNT(*) "
                    + "FROM producto "
                    + "WHERE Producto_id = ?;");

            statment.setInt(1, id);
            
            resultSet = statment.executeQuery();

            resultSet.first();
            
            return resultSet.getInt(1) > 0;
        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //@Abraham falta  resultSet.close();
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        return false;
    }

    @Override
    public int getTheId(String name) {
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT Producto_id "
                    + "FROM producto "
                    + "WHERE Producto_nombre = ?;");

            statment.setString(1, name);
            
            resultSet = statment.executeQuery();
            
            resultSet.next();
            return resultSet.getInt("Producto_id");
        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //@Abraham falta  resultSet.close();
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        return -1;
    }

    @Override
    public Product getProduct(int id) {
        Connection conexion = null;
        Statement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.createStatement();

            String numberOfSelectedUsersQuery = "SELECT * "
                    + "FROM producto "
                    + "WHERE Producto_id = " + id + ";";

            statment.execute(numberOfSelectedUsersQuery);
            resultSet = statment.getResultSet();
            
            resultSet.first();
            
            return new Product(resultSet.getInt("Producto_id"), 
                    resultSet.getString("Producto_nombre"), 
                    resultSet.getString("Producto_DescripcionCorta"), 
                    resultSet.getString("Producto_DescripcionLarga"), 
                    resultSet.getDouble("Producto_Precio"), 
                    resultSet.getInt("Producto_Stock"));

        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //@Abraham falta  resultSet.close();
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        return null;
    }

    @Override
    public void createProduct(Product product) {
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT Producto_id "
                    + "FROM producto "
                    + "ORDER BY Producto_id DESC;");
            
            resultSet = statment.executeQuery();
            resultSet.first();
            
            int id;
            if (resultSet.getRow() != 0) {
                id = resultSet.getInt("Producto_id");
            } else {
                id = 0;
            }
            id++;
            statment.close();

            statment = conexion.prepareStatement("INSERT INTO `shoppers`.`producto` "
                    + "(`Producto_id`, `Producto_nombre`, "
                    + "`Producto_DescripcionCorta`, `Producto_DescripcionLarga`, "
                    + "`Producto_Precio`, `Producto_Stock`) "
                    + "VALUES (?, ?, ?, ?, ?, ?);");
            
            statment.setInt(1, id);
            statment.setString(2, product.getName());
            statment.setString(3, product.getShortDescription());
            statment.setString(4, product.getLongDescription());
            statment.setDouble(5, product.getPrice());
            statment.setInt(6, product.getStock());
            
            statment.executeUpdate();
            //@Abraham falta  resultSet.close(); y cerrar en el finally
        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    @Override
    public void updateProduct(int id, Product product) {
        Connection conexion = null;
            PreparedStatement statement = null;
        
        try {
            conexion = pool.getConnection();
            statement = conexion.prepareStatement("UPDATE `shoppers`.`producto` "
                    + "SET `Producto_nombre`= ?, "
                    + "`Producto_DescripcionCorta`= ?, "
                    + "`Producto_DescripcionLarga`= ?, "
                    + "`Producto_Precio`= ?, "
                    + "`Producto_Stock`= ? "
                    + "WHERE `Producto_id`= ?;");
            
            statement.setString(1, product.getName());
            statement.setString(2, product.getShortDescription());
            statement.setString(3, product.getLongDescription());
            statement.setDouble(4, product.getPrice());
            statement.setInt(5, product.getStock());
            statement.setInt(6, id);
            
            statement.executeUpdate();
            //@Abraham Ahora no hacen falta estos dos aqui
            statement.close();
            conexion.close();
        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statement != null) {
                        try {
                            statement.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
    }

    @Override
    public void deleteProduct(int id) {
        Connection conexion = null;
        PreparedStatement statment = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("DELETE FROM `shoppers`.`producto` "
                    + "WHERE `Producto_id`= ?;");
            
            statment.setInt(1, id);
            
            statment.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if(conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }
    }

    @Override
    public List<Product> getALLTheProducts() {
        List<Product> products = new ArrayList();
        
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT * "
                    + "FROM producto;");

            resultSet = statment.executeQuery();
            
            while (resultSet.next()) {
            
            products.add(new Product(resultSet.getInt("Producto_id"), 
                    resultSet.getString("Producto_nombre"), 
                    resultSet.getString("Producto_DescripcionCorta"), 
                    resultSet.getString("Producto_DescripcionLarga"), 
                    resultSet.getDouble("Producto_Precio"), 
                    resultSet.getInt("Producto_Stock")));
            }
            
            return products;

        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //@Abraham falta  resultSet.close();
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(ProductPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        return new ArrayList();
    }

    @Override
    public void updateStock(int id, int cantidad) {
        Product producto = getProduct(id);        
        producto.setStock(producto.getStock()-cantidad);
        updateProduct(id, producto);        
    }
    
}
